﻿-- =============================================
-- Execute this after CreateDatabase
-- =============================================


use [dominium]
GO
/****** Object:  Table [dbo].[Address]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[Address]
       (
         [AddressId] [int] identity(1000 , 1)
                           not null
       , [AddressHash] [varchar](50) not null
       , [Line1] [varchar](50) null
       , [Line2] [varchar](50) null
       , [Line3] [varchar](50) null
       , [City] [varchar](50) null
       , [State] [char](2) null
       , [Zip] [varchar](10) null
       , [URL] [varchar](50) null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_Address] primary key clustered ( [AddressId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  Table [dbo].[CarrierLineOfBusiness]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create table [dbo].[CarrierLineOfBusiness]
       (
         [Id] [bigint] identity(1000 , 1)
                       not null
       , [LineOfBusinessId] [int] not null
       , [CarrierId] [int] not null
       , [Commission] [decimal](18 , 0) not null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_Carrier.LineOfBusiness] primary key clustered
            ( [Id] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
/****** Object:  Table [dbo].[ClientTypes]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[ClientTypes]
       (
         [ClientTypeId] [int] identity(1000 , 1)
                              not null
       , [ClientTypeName] [varchar](100) not null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , primary key clustered ( [ClientTypeId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  Table [dbo].[Contract]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[Contract]
       (
         [ContractId] [int] identity(1000 , 1)
                            not null
       , [ContractTitle] [varchar](50) null
       , [ContractNotes] [varchar](100) null
       , [ClientId] [int] not null
       , [StartDate] [datetime] not null
       , [EndDate] [datetime] null
       , [IsActive] [bit] null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK__Contract] primary key clustered ( [ContractId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  Table [dbo].[ContractReceipt]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[ContractReceipt]
       (
         [ReceiptId] [int] identity(1000 , 1)
                           not null
       , [ContractId] [int] not null
       , [CarrierId] [int] not null
       , [LineOfBusinessId] [int] not null
       , [RevenueTypeId] [int] not null
       , [ReceivedAmount] [money] not null
       , [ReceiptDate] [datetime2](7) not null
       , [ReceiptNotes] [varchar](100) null
       , [QB_InvoiceNum] [varchar](50) not null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK__ContractReceipt] primary key clustered
            ( [ReceiptId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
exec sys.sp_addextendedproperty @name = N'MS_Description' ,
    @value = N'this ammount has to total what in CarrierCommission Received amount' ,
    @level0type = N'SCHEMA' , @level0name = N'dbo' , @level1type = N'TABLE' ,
    @level1name = N'ContractReceipt' , @level2type = N'COLUMN' ,
    @level2name = N'ReceivedAmount'
GO
/****** Object:  Table [dbo].[ContractLineOfBusiness]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create table [dbo].[ContractLineOfBusiness]
       (
         [Id] [bigint] identity(1000 , 1)
                       not null
       , [LineOfBusinessId] [int] not null
       , [CarrierId] [int] not null
       , [InsuranceTypeId] [int] not null
       , [ContractId] [int] not null
       , [Commission] [decimal](18 , 0) not null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_ContractLineOfBusiness] primary key clustered
            ( [Id] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
/****** Object:  Table [dbo].[LineOfBusiness]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[LineOfBusiness]
       (
         [LineOfBusinessId] [int] identity(1000 , 1)
                                  not null
       , [LoBName] [varchar](50) not null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_LineOfBusiness] primary key clustered
            ( [LineOfBusinessId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  Table [dbo].[InsuranceType]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[InsuranceType]
       (
         [InsuranceTypeId] [int] identity(1000 , 1)
                                 not null
       , [InsuranceTypeName] [varchar](50) not null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , primary key clustered ( [InsuranceTypeId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  Table [dbo].[RevenueType]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[RevenueType]
       (
         [RevenueTypeId] [int] identity(1000 , 1)
                               not null
       , [RevenueTypeName] [varchar](50) not null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_AllocationType] primary key clustered
            ( [RevenueTypeId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  Table [dbo].[SalesTeam]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create table [dbo].[SalesTeam]
       (
         [Id] [int] identity(1000 , 1)
                    not null
       , [PersonId] [int] not null
       , [SalesRoleId] [int] not null
       , [DefaultComm] [decimal](2 , 0) null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] not null
       , constraint [PK_SalesTeam] primary key clustered ( [Id] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
/****** Object:  Table [dbo].[SalesRole]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[SalesRole]
       (
         [SalesRoleId] [int] identity(1000 , 1)
                             not null
       , [RoleName] [varchar](50) not null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_SalesRole] primary key clustered ( [SalesRoleId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  StoredProcedure [dbo].[UpdateAddress]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create proc [dbo].[UpdateAddress]
       (
         @Line1 varchar(50)
       , @Line2 varchar(50)
       , @Line3 varchar(50)
       , @City varchar(50)
       , @State varchar(50)
       , @Zip varchar(10)
       , @URL varchar(50)
       , @AddressId int 
       )
as 
       declare @AddressHash varchar(100)
       set @AddressHash = ( isnull(@Line1 , '') + isnull(@Line2 , '')
                            + isnull(@Line3 , '') + isnull(@City , '')
                            + isnull(@State , '') + isnull(@Zip , '') );
       set @AddressHash = ( select replace(@AddressHash , ' ' , '')
                          );
	
--Update table	

       begin

             update [dbo].[Address]
                set [Line1] = @Line1
                  , [Line2] = @Line2
                  , [Line3] = @Line3
                  , [City] = @City
                  , [State] = @State
                  , [Zip] = @Zip
                  , [URL] = @URL
                  , [AddressHash] = @AddressHash
                  , [Modified] = getdate()
                where AddressId = @AddressId;
       end
GO
/****** Object:  StoredProcedure [dbo].[AddSalesTeamMember]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create procedure [dbo].[AddSalesTeamMember]
       (
         @PersonId int
       , @SalesRoleId int
       , @DefaultComm decimal
          
       )
as 
       begin

             insert into [dominium].[dbo].[SalesTeam]
                    (
                      [PersonId]
                    , [SalesRoleId]
                    , [DefaultComm]
                    )
                values
                    (
                      @PersonId
                    , @SalesRoleId
                    , @DefaultComm
                    )
       end
GO
/****** Object:  Table [dbo].[Person]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[Person]
       (
         [PersonId] [int] identity(1000 , 1)
                          not null
       , [AddressId] [int] not null
       , [LastName] [varchar](30) not null
       , [FirstName] [varchar](30) not null
       , [MidName] [varchar](30) null
       , [Title] [varchar](50) null
       , [EmailAddress] [varchar](50) null
       , [WorkPhone] [varchar](50) null
       , [MobilePhone] [varchar](50) null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , primary key clustered ( [PersonId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  Table [dbo].[ContractEstimates]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[ContractEstimates]
       (
         [Id] [int] identity(1000 , 1)
                    not null
       , [ContractId] [int] not null
       , [EstimatedRevenue] [money] not null
       , [HeadCount] [int] not null
       , [Description] [varchar](100) not null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK__ContractEstimates] primary key clustered ( [Id] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  Table [dbo].[Client]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[Client]
       (
         [ClientId] [int] identity(1000 , 1)
                          not null
       , [ClientName] [varchar](100) not null
       , [ClientTypeId] [int] not null
       , [AddressId] [int] not null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [IsActive] [bit] not null
       , [InActiveSince] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , primary key clustered ( [ClientId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  Table [dbo].[Carrier]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[Carrier]
       (
         [CarrierId] [int] identity(1000 , 1)
                           not null
       , [CarrierName] [varchar](50) not null
       , [AddressId] [int] null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_Carrier] primary key clustered ( [CarrierId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  StoredProcedure [dbo].[AddContract]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create proc [dbo].[AddContract]
       (
         @ClientId int
       , @StartDate datetime
       , @ContractNotes varchar(100)
       , @ContractTitle varchar(50)
       , @ContractId int output
       )
as 
       begin

             insert into [dbo].[Contract]
                    (
                      [ClientId]
                    , [StartDate]
                    , [ContractNotes]
                    , [IsActive]
                    )
                values
                    (
                      @ClientId
                    , @StartDate
                    , @ContractNotes
                    , 1
                    );
		
             set @ContractId = ( select scope_identity()
                               )
		
       end
GO
/****** Object:  StoredProcedure [dbo].[AddContractsLinesOB]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create proc [dbo].[AddContractsLinesOB]
       (
         @LOfBId int
       , @ContractId int
       , @CarrierId int
       , @InsuranceTypeId int
       , @Commission decimal
       )
as 
       begin
             insert into ContractLineOfBusiness
                    (
                      LineOfBusinessId
                    , ContractId
                    , CarrierId
                    , InsuranceTypeId
                    , Commission
                    )
                values
                    (
                      @LOfBId
                    , @ContractId
                    , @CarrierId
                    , @InsuranceTypeId
                    , @Commission
                    );

       end
GO
/****** Object:  StoredProcedure [dbo].[AddAddress]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create proc [dbo].[AddAddress]
       (
         @Line1 varchar(50)
       , @Line2 varchar(50) = null
       , @Line3 varchar(50) = null
       , @City varchar(50)
       , @State varchar(50)
       , @Zip varchar(10)
       , @URL varchar(50) = null
       , @AddressId int output
       )
as 
       declare @AddressHash varchar(50)
	
       set @AddressHash = ( isnull(@Line1 , '') + isnull(@Line2 , '')
                            + isnull(@Line3 , '') + isnull(@City , '')
                            + isnull(@State , '') + isnull(@Zip , '') );
       set @AddressHash = lower(( select replace(@AddressHash , ' ' , '')
                                ));
	

	---Insert Address
	---But Check if Address exists based on Hash
       if not exists ( select 1
                        from dbo.Address
                        where AddressHash like ( '%' + @AddressHash + '%' ) ) 
          begin
                insert into [dbo].[Address]
                        (
                          [Line1]
                        , [Line2]
                        , [Line3]
                        , [City]
                        , [State]
                        , [Zip]
                        , [URL]
                        , [AddressHash]
                        )
                    values
                        (
                          @Line1
                        , @Line2
                        , @Line3
                        , @City
                        , @State
                        , @Zip
                        , @URL
                        , @AddressHash
                        );
			 
                set @AddressId = ( select AddressId
                                    from dbo.Address
                                    where AddressHash like ( '%'
                                                             + @AddressHash
                                                             + '%' )
                                 );
		 
          end 
       set @AddressId = ( select AddressId
                            from dbo.Address
                            where AddressHash like ( '%' + @AddressHash + '%' )
                        );
GO
/****** Object:  StoredProcedure [dbo].[AddCarrier]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create procedure [dbo].[AddCarrier]
       (
         @CarrierName varchar(50)
       , @AddressId int
       , @CarrierId int output
       )
as 
       begin
             insert into dbo.Carrier
                    ( [CarrierName] , [AddressId] )
                values ( @CarrierName , @AddressId );
	
             set @CarrierId = ( select CarrierId
                                    from dbo.Carrier
                                    where CarrierName = @CarrierName
                                        and AddressId = @AddressId
                              );
       end
GO
/****** Object:  StoredProcedure [dbo].[AddClient]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create procedure [dbo].[AddClient]
       (
         @ClientName varchar(100)
       , @ClientTypeId int
       , @AddressId int
       , @ClientId int output
       )
as 
       begin
             insert into [dbo].[Client]
                    (
                      [ClientName]
                    , [ClientTypeId]
                    , [AddressId]
                    , [IsActive]
                    )
                values
                    (
                      @ClientName
                    , @ClientTypeId
                    , @AddressId
                    , 1
                    );
	
             set @ClientId = ( select ClientId
                                from [dbo].[Client]
                                where ClientName = @ClientName
                                    and AddressId = @AddressId
                             );
       end
GO
/****** Object:  StoredProcedure [dbo].[AddContractEstimates]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create proc [dbo].[AddContractEstimates]
       (
         @ContractId int
       , @EstimatedRevenue money
       , @HeadCount int
       , @Description varchar(100)
       )
as 
       begin
             insert into [dbo].[ContractEstimates]
                    (
                      [ContractId]
                    , [EstimatedRevenue]
                    , [HeadCount]
                    , [Description] 
                    )
                values
                    (
                      @ContractId
                    , @EstimatedRevenue
                    , @HeadCount
                    , @Description
                    );
       end
GO
/****** Object:  StoredProcedure [dbo].[AddPerson]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create proc [dbo].[AddPerson]
       (
         @FirstName varchar(30)
       , @LastName varchar(30)
       , @MidName varchar(30) = null
       , @Title varchar(30) = null
       , @EmailAddress varchar(50) = null
       , @WorkPhone varchar(50) = null
       , @MobilePhone varchar(50) = null
       , @AddressId int
       , @PersonId int output
       )
as 
       begin
             if not exists ( select PersonId
                                from Person
                                where FirstName like @FirstName
                                    and LastName like @LastName ) 
                begin
                      insert into [dbo].[Person]
                            (
                              [AddressId]
                            , [LastName]
                            , [FirstName]
                            , [MidName]
                            , [Title]
                            , [EmailAddress]
                            , [WorkPhone]
                            , [MobilePhone]
                            )
                        values
                            (
                              @AddressId
                            , @LastName
                            , @FirstName
                            , @MidName
                            , @Title
                            , @EmailAddress
                            , @WorkPhone
                            , @MobilePhone
                            );				  
                end
       end
	
       set @PersonId = ( select PersonId
                            from Person
                            where FirstName like @FirstName
                                and LastName like @LastName
                       )
GO
/****** Object:  Table [dbo].[CarrierCommission]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[CarrierCommission]
       (
         [Id] [bigint] identity(1000 , 1)
                       not null
       , [CarrierId] [int] not null
       , [RevenueTypeId] [int] not null
       , [Commission_ReceivedAmt] [money] not null
       , [Commission] [decimal](18 , 0) null
       , [ReceiptDate] [datetime] not null
       , [QB_InvoiceNum] [varchar](50) null
       , [IsItemized] [bit] not null
       , [Notes] [varchar](100) null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_CarrierCommission] primary key clustered ( [Id] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  Table [dbo].[BonusPool]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[BonusPool]
       (
         [PoolingId] [bigint] identity(1000 , 1)
                              not null
       , [PersonId] [int] not null
       , [ContractId] [int] not null
       , [CommissionPooled] [decimal](18 , 0) null
       , [PooledAmount] [money] null
       , [AccruedPool] [money] null
       , [DatePooled] [datetime] not null
       , [Notes] [varchar](100) null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_SalesPersonPooling] primary key clustered
            ( [PoolingId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  Table [dbo].[ContactPerson]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create table [dbo].[ContactPerson]
       (
         [id] [int] identity(1000 , 1)
                    not null
       , [PersonId] [int] not null
       , [ClientId] [int] null
       , [IsPrimaryContact] [bit] not null
       , [SendCard] [bit] not null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_Client.ContactPerson] primary key clustered
            ( [id] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
/****** Object:  Table [dbo].[ProducerPayOut]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
set ANSI_PADDING on
GO
create table [dbo].[ProducerPayOut]
       (
         [PayOutId] [bigint] identity(1000 , 1)
                             not null
       , [PersonId] [int] not null
       , [ContractId] [int] not null
       , [PaidAmount] [money] null
       , [PaidComm] [decimal](18 , 0) not null
       , [PayDate] [datetime] not null
       , [Notes] [varchar](100) null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_SalesPersonPayOut] primary key clustered
            ( [PayOutId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
set ANSI_PADDING off
GO
/****** Object:  StoredProcedure [dbo].[UpdateClient]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create procedure [dbo].[UpdateClient]
       (
         @ClientName varchar(100)
       , @ClientTypeId int
       , @ClientId int
       , @AddressId int 
       )
as 
       begin 
             update [dbo].[Client]
                set ClientName = @ClientName
                  , ClientTypeId = @ClientTypeId
                  , AddressId = @AddressId
                  , Modified = getdate()
                where ClientId = @ClientId;
       end
GO
/****** Object:  Table [dbo].[SalesInfo]    Script Date: 06/06/2011 08:17:31 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create table [dbo].[SalesInfo]
       (
         [SalesId] [int] identity(1000 , 1)
                         not null
       , [ContractId] [int] not null
       , [PersonId] [int] not null
       , [SalesRoleId] [int] not null
       , [RevenueTypeId] [int] null
       , [Commission] [decimal](2 , 0) not null
       , [Fee] [money] null
       , [StartDate] [datetime] not null
       , [EndDate] [datetime] null
       , [Created] [datetime] not null
       , [Modified] [datetime] null
       , [Uid] [uniqueidentifier] rowguidcol
                                  not null
       , constraint [PK_SalesInfo] primary key clustered ( [SalesId] asc )
            with ( pad_index = off , statistics_norecompute = off ,
                   ignore_dup_key = off , allow_row_locks = on ,
                   allow_page_locks = on ) on [PRIMARY]
       )
on     [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[UpdateInvoiceIsItemizedFlag]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create procedure [dbo].[UpdateInvoiceIsItemizedFlag]
       (
         @QB_InvoiceNum varchar(50)
       )
as 
       begin	 
             update dbo.CarrierCommission
                set IsItemized = 1
                where QB_InvoiceNum = @QB_InvoiceNum;
       end
GO
/****** Object:  StoredProcedure [dbo].[UpdateContactPersonPrefs]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create proc [dbo].[UpdateContactPersonPrefs]
       (
         @ClientId int = null
       , @IsPrimaryContact bit = 1
       , @SendCard bit = 1
       , @PersonId int
       )
as 
       begin

	--Update ContactPersons
             update [dbo].[ContactPerson]
                set [IsPrimaryContact] = @IsPrimaryContact
                  , [SendCard] = @SendCard
                  , [Modified] = getdate()
                where [PersonId] = @PersonId
                    and ClientId = @ClientId;
		 
		 
		 
       end
GO
/****** Object:  StoredProcedure [dbo].[GetClientContactPerson]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create procedure [dbo].[GetClientContactPerson] ( @ClientId int )
as 
       set nocount on;
       select Client.ClientId
          , ContactPerson.PersonId
          , ContactPerson.IsPrimaryContact
          , ContactPerson.SendCard
          , Person.MidName
          , Person.FirstName
          , Person.LastName
          , Person.Title
          , Person.EmailAddress
          , Person.WorkPhone
          , Person.MobilePhone
        from Client
        inner join ContactPerson
        on  Client.ClientId = ContactPerson.ClientId 
        inner join Person
        on  ContactPerson.PersonId = Person.PersonId
        where ( Client.ClientId = @ClientId );
GO
/****** Object:  StoredProcedure [dbo].[GetAllClients]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create procedure [dbo].[GetAllClients]
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
as /* SET NOCOUNT ON */
	
       select c.ClientId
          , c.ClientName
          , p.FirstName + ' ' + p.LastName as ContactPerson
          , Line1 as Address1
          , Line2 as Address2
          , Line3 as Address3
          , City
          , State
          , Zip
          , URL
          , ct.ClientTypeName
        from dbo.Client c
        inner join dbo.ClientTypes ct
        on  c.ClientTypeId = ct.ClientTypeId
        inner join dbo.Address a
        on  c.AddressId = a.AddressId
        inner join dbo.ContactPerson cp
        on  c.ClientId = cp.ClientId 
        inner join dbo.Person p
        on  cp.PersonId = p.PersonId
        where c.IsActive = 1
        order by c.ClientName asc;
       return
GO
/****** Object:  StoredProcedure [dbo].[AllocateProducerPayOut]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create procedure [dbo].[AllocateProducerPayOut]
       (
         @PersonId int
       , @ContractId int
       , @PaidAmount money
       , @PaidComm decimal
       , @DatePaid datetime
       , @QB_InvoiceNum nvarchar(50)
       )
as 
       begin
             if not exists ( select 1
                                from dbo.ProducerPayOut
                                where ContractId = @ContractId
                                    and PersonId = @ContractId )
	-- One payout per contract per Invoice
                if not exists ( select 1
                                    from dbo.ProducerPayOut pp 
                                    inner join ContractReceipt cr
                                    on  pp.ContractId = cr.ContractId
                                    where (
                                            pp.ContractId = @ContractId
                                            and pp.PersonId = @PersonId
                                            and cr.QB_InvoiceNum = @QB_InvoiceNum
                                          ) ) 
                   begin
                         insert into dbo.ProducerPayOut
                                (
                                  PersonId
                                , ContractId
                                , PaidAmount
                                , PaidComm
                                , PayDate 
                                )
                            values
                                (
                                  @PersonId
                                , @ContractId
                                , @PaidAmount
                                , @PaidComm
                                , @DatePaid
                                );
                   end
       end
GO
/****** Object:  StoredProcedure [dbo].[AllocateProducerBonusPool]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create procedure [dbo].[AllocateProducerBonusPool]
       (
         @PersonId int
       , @ContractId int
       , @PoolAmount money
       , @AccruedPool money
       , @PoolComm decimal
       , @DatePooled datetime
       , @QB_InvoiceNum nvarchar(50)
       )
as 
       begin
			-- One Bonus pool per contract per Invoice
             if not exists ( select 1
                                from dbo.BonusPool bp 
                                inner join ContractReceipt cr
                                on  bp.ContractId = cr.ContractId
                                where (
                                        bp.ContractId = @ContractId
                                        and PersonId = @PersonId
                                        and cr.QB_InvoiceNum = @QB_InvoiceNum
                                      ) ) 
                begin
                      insert into dbo.BonusPool
                            (
                              PersonId
                            , ContractId
                            , PooledAmount
                            , AccruedPool
                            , CommissionPooled
                            , DatePooled
                            )
                        values
                            (
                              @PersonId
                            , @ContractId
                            , @PoolAmount
                            , @AccruedPool
                            , @PoolComm
                            , @DatePooled 
                            );
                end
       end
GO
/****** Object:  StoredProcedure [dbo].[AddContractsSalesPersonel]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create proc [dbo].[AddContractsSalesPersonel]
       (
         @ContractId int
       , @PersonId int
       , @SalesRoleId int
       , @RevenueTypeId int
       , @Commission decimal
       , @Fee money
       , @StartDate datetime

       )
as 
       begin
             if not exists ( select 1
                                from dbo.SalesInfo
                                where ContractId = @ContractId
                                    and PersonId = @PersonId ) 
                begin
                      insert into dbo.SalesInfo
                            (
                              ContractId
                            , PersonId
                            , SalesRoleId
                            , RevenueTypeId
                            , Commission
                            , Fee
                            , StartDate
                            )
                        values
                            (
                              @ContractId
                            , @PersonId
                            , @SalesRoleId
                            , @RevenueTypeId
                            , @Commission
                            , @Fee
                            , @StartDate  
                            );
                end
       end
GO
/****** Object:  StoredProcedure [dbo].[AddContactPerson]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create  proc [dbo].[AddContactPerson]
       (
         @ClientId int = null
       , @IsPrimaryContact bit = 1
       , @SendCard bit = 1
       , @PersonId int 
	
       )
as 
       set NOCOUNT on;
       begin
	---Insert into ContactPersons
             insert into [dbo].[ContactPerson]
                    (
                      [ClientId]
                    , [IsPrimaryContact]
                    , [SendCard]
                    , [PersonId]
                    )
                values
                    (
                      @ClientId
                    , @IsPrimaryContact
                    , @SendCard
                    , @PersonId
                    );
				   
	

       end
GO
/****** Object:  StoredProcedure [dbo].[AddClientContractReceipt]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
-- Batch submitted through debugger: SQLQuery11.sql|0|0|C:\Users\twachira\AppData\Local\Temp\~vs3674.sql


--insert into CarrierCommission
--( CarrierId, ContractId, RevenueTypeId, Bonus_Amt, Commission_Amt, Commission, ReceiptDate, Notes)
--values
--();

create procedure [dbo].[AddClientContractReceipt]
       (
         @ContractId int
       , @CarrierId int
       , @RevenueTypeId int
       , @LineOfBusinessId int
       , @Amt_Rcvd money
       , @ReceiptDate datetime
       , @ReceiptNotes varchar(100)
       , @QB_InvoiceNum varchar(50)
       )
as 
       begin
             insert into dbo.ContractReceipt
                    (
                      ContractId
                    , CarrierId
                    , RevenueTypeId
                    , LineOfBusinessId
                    , ReceivedAmount
                    , ReceiptDate
                    , ReceiptNotes
                    , QB_InvoiceNum
			        )
                values
                    (
                      @ContractId
                    , @CarrierId
                    , @RevenueTypeId
                    , @LineOfBusinessId
                    , @Amt_Rcvd
                    , @ReceiptDate
                    , @ReceiptNotes
                    , @QB_InvoiceNum
			        );
			 
             update dbo.CarrierCommission
                set IsItemized = 1
                where QB_InvoiceNum = @QB_InvoiceNum;
       end
GO
/****** Object:  StoredProcedure [dbo].[AddCarrierCommissions]    Script Date: 06/06/2011 08:17:32 ******/
set ANSI_NULLS on
GO
set QUOTED_IDENTIFIER on
GO
create procedure [dbo].[AddCarrierCommissions]
       (
         @CarrierId int
       , @RevenueTypeId int = null
       , @Commission_ReceivedAmt money
       , @Commission decimal = null
       , @ReceiptDate datetime
       , @QB_InvoiceNum varchar(50)
       )
as 
       begin
             if ( @RevenueTypeId = null ) 
                begin
                      set @RevenueTypeId = ( select RevenueTypeId
                                                from dbo.RevenueType
                                                where RevenueTypeName = 'Commission'
                                           );
                end

             if not exists ( select 1
                                from dbo.CarrierCommission
                                where ReceiptDate like @ReceiptDate
                                    and QB_InvoiceNum = @QB_InvoiceNum ) 
                begin
                      insert into dbo.CarrierCommission
                            (
                              CarrierId
                            , RevenueTypeId
                            , Commission_ReceivedAmt
                            , Commission
                            , ReceiptDate
                            , QB_InvoiceNum
                            )
                        values
                            (
                              @CarrierId
                            , @RevenueTypeId
                            , @Commission_ReceivedAmt
                            , @Commission
                            , @ReceiptDate
                            , @QB_InvoiceNum
                            );
                end
       end
GO
/****** Object:  Default [DF__Address__Created__3B75D760]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Address] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__Address__Uid__3C69FB99]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Address] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__SalesPers__Creat__46E78A0C]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[BonusPool] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__SalesPerson__Uid__47DBAE45]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[BonusPool] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__Carrier__Created__3D5E1FD2]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Carrier] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__Carrier__Uid__3E52440B]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Carrier] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF_CarrierCommission_IsItemized]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[CarrierCommission] add  constraint [DF_CarrierCommission_IsItemized]  default ((0)) for [IsItemized]
GO
/****** Object:  Default [DF__CarrierCo__Creat__4E88ABD4]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[CarrierCommission] add  constraint [DF__CarrierCo__Creat__4E88ABD4]  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__CarrierComm__Uid__4F7CD00D]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[CarrierCommission] add  constraint [DF__CarrierComm__Uid__4F7CD00D]  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__CarrierLi__Creat__11158940]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[CarrierLineOfBusiness] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__CarrierLine__Uid__1209AD79]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[CarrierLineOfBusiness] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__Client__Created__3F466844]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Client] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__Client__Uid__403A8C7D]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Client] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__ClientTyp__Creat__398D8EEE]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ClientTypes] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__ClientTypes__Uid__3A81B327]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ClientTypes] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__ContactPe__IsPri__5070F446]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContactPerson] add  default ((1)) for [IsPrimaryContact]
GO
/****** Object:  Default [DF__ContactPe__SendC__5165187F]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContactPerson] add  default ((0)) for [SendCard]
GO
/****** Object:  Default [DF__ContactPe__Creat__52593CB8]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContactPerson] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__ContactPers__Uid__534D60F1]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContactPerson] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__Contract__Create__37A5467C]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Contract] add  constraint [DF__Contract__Create__37A5467C]  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__Contract__Uid__38996AB5]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Contract] add  constraint [DF__Contract__Uid__38996AB5]  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__ContractE__Creat__44FF419A]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContractEstimates] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__ContractEst__Uid__45F365D3]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContractEstimates] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF_ContractLineOfBusiness_Created]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContractLineOfBusiness] add  constraint [DF_ContractLineOfBusiness_Created]  default (getdate()) for [Created]
GO
/****** Object:  Default [DF_ContractLineOfBusiness_Uid]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContractLineOfBusiness] add  constraint [DF_ContractLineOfBusiness_Uid]  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__ContractR__Creat__4316F928]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContractReceipt] add  constraint [DF__ContractR__Creat__4316F928]  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__ContractRec__Uid__440B1D61]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContractReceipt] add  constraint [DF__ContractRec__Uid__440B1D61]  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__Insurance__Creat__35BCFE0A]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[InsuranceType] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__InsuranceTy__Uid__36B12243]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[InsuranceType] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__LineOfBus__Creat__33D4B598]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[LineOfBusiness] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__LineOfBusin__Uid__34C8D9D1]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[LineOfBusiness] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__Person__Created__412EB0B6]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Person] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__Person__Uid__4222D4EF]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Person] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__SalesPers__Creat__48CFD27E]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ProducerPayOut] add  constraint [DF__SalesPers__Creat__48CFD27E]  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__SalesPerson__Uid__49C3F6B7]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ProducerPayOut] add  constraint [DF__SalesPerson__Uid__49C3F6B7]  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__RevenueTy__Creat__31EC6D26]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[RevenueType] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__RevenueType__Uid__32E0915F]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[RevenueType] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF_SalesInfo_Commission]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesInfo] add  constraint [DF_SalesInfo_Commission]  default ((0)) for [Commission]
GO
/****** Object:  Default [DF_SalesInfo_Fee]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesInfo] add  constraint [DF_SalesInfo_Fee]  default ((0.00)) for [Fee]
GO
/****** Object:  Default [DF__SalesInfo__Creat__4AB81AF0]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesInfo] add  constraint [DF__SalesInfo__Creat__4AB81AF0]  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__SalesInfo__Uid__4BAC3F29]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesInfo] add  constraint [DF__SalesInfo__Uid__4BAC3F29]  default (newid()) for [Uid]
GO
/****** Object:  Default [DF__SalesRole__Creat__300424B4]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesRole] add  default (getdate()) for [Created]
GO
/****** Object:  Default [DF__SalesRole__Uid__30F848ED]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesRole] add  default (newid()) for [Uid]
GO
/****** Object:  Default [DF_SalesTeam_Commission]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesTeam] add  constraint [DF_SalesTeam_Commission]  default ((0)) for [DefaultComm]
GO
/****** Object:  Default [DF__SalesTeam__Creat__05D8E0BE]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesTeam] add  constraint [DF__SalesTeam__Creat__05D8E0BE]  default (getdate()) for [Created]
GO
/****** Object:  Default [DF_SalesTeam_Uid]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesTeam] add  constraint [DF_SalesTeam_Uid]  default (newid()) for [Uid]
GO
/****** Object:  ForeignKey [FK_SalesPersonPooling_Contract]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[BonusPool]  with check add  constraint [FK_SalesPersonPooling_Contract] foreign key([ContractId])
references [dbo].[Contract] ([ContractId])
GO
alter table [dbo].[BonusPool] check constraint [FK_SalesPersonPooling_Contract]
GO
/****** Object:  ForeignKey [FK_SalesPersonPooling_Person]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[BonusPool]  with check add  constraint [FK_SalesPersonPooling_Person] foreign key([PersonId])
references [dbo].[Person] ([PersonId])
GO
alter table [dbo].[BonusPool] check constraint [FK_SalesPersonPooling_Person]
GO
/****** Object:  ForeignKey [FK_Carrier_Address]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Carrier]  with check add  constraint [FK_Carrier_Address] foreign key([AddressId])
references [dbo].[Address] ([AddressId])
GO
alter table [dbo].[Carrier] check constraint [FK_Carrier_Address]
GO
/****** Object:  ForeignKey [FK_CarrierCommission_Carrier]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[CarrierCommission]  with check add  constraint [FK_CarrierCommission_Carrier] foreign key([CarrierId])
references [dbo].[Carrier] ([CarrierId])
GO
alter table [dbo].[CarrierCommission] check constraint [FK_CarrierCommission_Carrier]
GO
/****** Object:  ForeignKey [FK_CarrierCommission_Carrier1]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[CarrierCommission]  with check add  constraint [FK_CarrierCommission_Carrier1] foreign key([CarrierId])
references [dbo].[Carrier] ([CarrierId])
GO
alter table [dbo].[CarrierCommission] check constraint [FK_CarrierCommission_Carrier1]
GO
/****** Object:  ForeignKey [FK_CarrierCommission_RevenueType]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[CarrierCommission]  with check add  constraint [FK_CarrierCommission_RevenueType] foreign key([RevenueTypeId])
references [dbo].[RevenueType] ([RevenueTypeId])
GO
alter table [dbo].[CarrierCommission] check constraint [FK_CarrierCommission_RevenueType]
GO
/****** Object:  ForeignKey [FK_Client_Address]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Client]  with check add  constraint [FK_Client_Address] foreign key([AddressId])
references [dbo].[Address] ([AddressId])
GO
alter table [dbo].[Client] check constraint [FK_Client_Address]
GO
/****** Object:  ForeignKey [FK_Client_ClientTypes]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Client]  with check add  constraint [FK_Client_ClientTypes] foreign key([ClientTypeId])
references [dbo].[ClientTypes] ([ClientTypeId])
GO
alter table [dbo].[Client] check constraint [FK_Client_ClientTypes]
GO
/****** Object:  ForeignKey [FK_ContactPerson_Client]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContactPerson]  with check add  constraint [FK_ContactPerson_Client] foreign key([ClientId])
references [dbo].[Client] ([ClientId])
GO
alter table [dbo].[ContactPerson] check constraint [FK_ContactPerson_Client]
GO
/****** Object:  ForeignKey [FK_ContactPerson_Person]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContactPerson]  with check add  constraint [FK_ContactPerson_Person] foreign key([PersonId])
references [dbo].[Person] ([PersonId])
GO
alter table [dbo].[ContactPerson] check constraint [FK_ContactPerson_Person]
GO
/****** Object:  ForeignKey [FK_ContractEstimates_Contract]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ContractEstimates]  with check add  constraint [FK_ContractEstimates_Contract] foreign key([ContractId])
references [dbo].[Contract] ([ContractId])
GO
alter table [dbo].[ContractEstimates] check constraint [FK_ContractEstimates_Contract]
GO
/****** Object:  ForeignKey [FK_Person_Address]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[Person]  with check add  constraint [FK_Person_Address] foreign key([AddressId])
references [dbo].[Address] ([AddressId])
GO
alter table [dbo].[Person] check constraint [FK_Person_Address]
GO
/****** Object:  ForeignKey [FK_SalesPersonPayOut_Contract]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ProducerPayOut]  with check add  constraint [FK_SalesPersonPayOut_Contract] foreign key([ContractId])
references [dbo].[Contract] ([ContractId])
GO
alter table [dbo].[ProducerPayOut] check constraint [FK_SalesPersonPayOut_Contract]
GO
/****** Object:  ForeignKey [FK_SalesPersonPayOut_Person]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[ProducerPayOut]  with check add  constraint [FK_SalesPersonPayOut_Person] foreign key([PersonId])
references [dbo].[Person] ([PersonId])
GO
alter table [dbo].[ProducerPayOut] check constraint [FK_SalesPersonPayOut_Person]
GO
/****** Object:  ForeignKey [FK_SalesInfo_Contract]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesInfo]  with check add  constraint [FK_SalesInfo_Contract] foreign key([ContractId])
references [dbo].[Contract] ([ContractId])
GO
alter table [dbo].[SalesInfo] check constraint [FK_SalesInfo_Contract]
GO
/****** Object:  ForeignKey [FK_SalesInfo_Person]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesInfo]  with check add  constraint [FK_SalesInfo_Person] foreign key([PersonId])
references [dbo].[Person] ([PersonId])
GO
alter table [dbo].[SalesInfo] check constraint [FK_SalesInfo_Person]
GO
/****** Object:  ForeignKey [FK_SalesInfo_RevenueType]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesInfo]  with check add  constraint [FK_SalesInfo_RevenueType] foreign key([RevenueTypeId])
references [dbo].[RevenueType] ([RevenueTypeId])
GO
alter table [dbo].[SalesInfo] check constraint [FK_SalesInfo_RevenueType]
GO
/****** Object:  ForeignKey [FK_SalesInfo_SalesRole]    Script Date: 06/06/2011 08:17:31 ******/
alter table [dbo].[SalesInfo]  with check add  constraint [FK_SalesInfo_SalesRole] foreign key([SalesRoleId])
references [dbo].[SalesRole] ([SalesRoleId])
GO
alter table [dbo].[SalesInfo] check constraint [FK_SalesInfo_SalesRole]
GO
